package dao.impl;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import boerrors.ClientSocieteExistantException;
import boerrors.ClientSocieteInexistantException;
import dao.ClientSocieteDao;
import dao.dto.ClientSocieteDto;
import dao.dto.AdresseDto;
import dao.dto.CodePostalDto;
import dao.dto.FormeDto;
import dao.dto.ZoneDto;

public class ClientSocieteDaoImpl extends ClientSocieteDao {
    private static ClientSocieteDao _instance = null;
    private CallableStatement _callableStatement = null;
    private SQLConnection _connection = new SQLConnection();

    //Constructeur du singleton
    public static ClientSocieteDao getInstance(){
            if(_instance == null)
                    _instance = new ClientSocieteDaoImpl();
            return _instance;
    }

   @Override
    public int enregistrer(ClientSocieteDto clientSociete) throws ClientSocieteExistantException {
            try {
                    //query
                    _callableStatement = _connection.getConnection().prepareCall("{? = call sp_ClientSocieteInsert(?,?,?,?,?,?,?,?,?,?)}");
                    // valeur de retour (id)                   
                    _callableStatement.registerOutParameter(1, java.sql.Types.INTEGER);
                    //parameter                      
                    _callableStatement.setString("nom",clientSociete.getNom());
                    _callableStatement.setString("prenom",clientSociete.getPrenom());
                    _callableStatement.setString("telephone",clientSociete.getTelephone());
                    _callableStatement.setString("email",clientSociete.getEmail());
                    _callableStatement.setString("numeroTVA",clientSociete.getNumeroTVA());
                    _callableStatement.setString("rue",clientSociete.getAdresse().getRue());
                    _callableStatement.setString("numero",clientSociete.getAdresse().getNumero());
                    _callableStatement.setInt("CodePostalFK",clientSociete.getAdresse().getCodePostal().getIdCodePostal());
                    _callableStatement.setString("nomSociete",clientSociete.getNomSociete());
                    _callableStatement.setInt("formeFK",clientSociete.getForme().getIdForme());

                    //execute
                    _callableStatement.executeUpdate();    
                    return _callableStatement.getInt(1);
            } catch(SQLException e) {
                if (e.getMessage().equals("547")) {
                    throw new ClientSocieteExistantException("Code Postal/Adresse/forme juridique Incorrect (FK)",e);
                } else if (e.getMessage().equals("515")) {
                    throw new ClientSocieteExistantException("Non autorisé champs vide",e);
                } else  throw new ClientSocieteExistantException(e);
            } catch(Exception e){ 
                throw new ClientSocieteExistantException(e);
            } finally {
                    if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {throw new ClientSocieteExistantException(e);}
                    _connection.deconnecter();
            }
    }  

    @Override
    public void modifier(ClientSocieteDto clientSociete) throws ClientSocieteInexistantException {
    try {
                    //query
                    _callableStatement = _connection.getConnection().prepareCall("{call sp_ClientSocieteUpdate(?,?,?,?,?,?,?,?,?,?,?)}");
                    //parameter 
                    _callableStatement.setInt("idClient",clientSociete.getIdClient());
                    _callableStatement.setString("nom",clientSociete.getNom());
                    _callableStatement.setString("prenom",clientSociete.getPrenom());
                    _callableStatement.setString("telephone",clientSociete.getTelephone());
                    _callableStatement.setString("email",clientSociete.getEmail());
                    _callableStatement.setString("numeroTVA",clientSociete.getNumeroTVA());
                  //  _callableStatement.setInt("idAdresse",clientSociete.getAdresse().getIdAdresse());
                    _callableStatement.setString("rue",clientSociete.getAdresse().getRue());
                    _callableStatement.setString("numero",clientSociete.getAdresse().getNumero());
                    _callableStatement.setInt("CodePostalFK",clientSociete.getAdresse().getCodePostal().getIdCodePostal());
                    _callableStatement.setString("nomSociete",clientSociete.getNomSociete());
                    _callableStatement.setInt("formeFK",clientSociete.getForme().getIdForme());

                    //execute
                    _callableStatement.executeUpdate();    
            } catch(SQLException e) {
                if (e.getMessage().equals("547")) {
                    throw new ClientSocieteInexistantException("Code postal/Adresse/forme juridique Incorrect (FK)",e);
                } else if (e.getMessage().equals("515")) {
                    throw new ClientSocieteInexistantException("Non autorisé champs vide",e);
                } else if (e.getMessage().equals("50001")) {
                    throw new ClientSocieteInexistantException("Le client n'existe pas",e);
                } else if (e.getMessage().equals("50002")) {
                    throw new ClientSocieteInexistantException("L'adresse n'a pas été trouvé",e);
                } else  throw new ClientSocieteInexistantException(e);
            } catch(Exception e){
                throw new ClientSocieteInexistantException(e);
            } finally {
                    if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {throw new ClientSocieteInexistantException(e);}
                    _connection.deconnecter();
            }
    }

    @Override
    public void supprimer(ClientSocieteDto clientSociete) throws ClientSocieteInexistantException {
            try {
                    //query
                    _callableStatement = _connection.getConnection().prepareCall("{call sp_ClientSocieteDelete(?)}");

                    //parameter                                               
                    _callableStatement.setInt("idClient",clientSociete.getIdClient());                      

                    //execute
                    _callableStatement.executeUpdate();    
            } catch(SQLException e) {
                if (e.getMessage().equals("50001")) {
                    throw new ClientSocieteInexistantException("Le client n'existe pas",e);
                } else if (e.getMessage().equals("50002")) {
                    throw new ClientSocieteInexistantException("Suppression non autorisée: client utilisé dans les interventions",e);
                } else  throw new ClientSocieteInexistantException(e);                  
            } catch(Exception e){throw new ClientSocieteInexistantException(e);
            } finally {
                    if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {throw new ClientSocieteInexistantException(e);}
                    _connection.deconnecter();}
    }
        
    @Override
    public Vector<ClientSocieteDto> lister() throws ClientSocieteInexistantException {
        ResultSet resultat = null;
        ZoneDto zone = null;
        CodePostalDto codePostal = null;
        AdresseDto adresse = null;
        FormeDto forme = null;
        ClientSocieteDto client = null;
            try {                                      
                //query
                _callableStatement = _connection.getConnection().prepareCall("{call sp_ClientSocieteList}");
                //parameter

                //execute
                resultat = _callableStatement.executeQuery();
                Vector<ClientSocieteDto> liste = new Vector<ClientSocieteDto>();
                
                while (resultat.next()) {
                                      
                    zone = new ZoneDto( resultat.getInt("idZone"),
                                        resultat.getString("libZone"));

                    codePostal = new CodePostalDto( resultat.getInt("idCodePostal"),
                                                    resultat.getString("cp"),
                                                    resultat.getString("ville"),
                                                    resultat.getString("libPays"),
                                                    zone);
                    adresse = new AdresseDto(   resultat.getInt("idAdresse"),
                                                resultat.getString("rue"),
                                                resultat.getString("numero"),
                                                codePostal
                                                );
                    
                    forme = new FormeDto(resultat.getInt("idForme"),
                                        resultat.getString("libForme"));
                    
                    client = new ClientSocieteDto(  resultat.getInt("idClient"),
                                                        resultat.getString("nom"),
                                                        resultat.getString("prenom"),
                                                        adresse,
                                                        resultat.getString("telephone"),
                                                        resultat.getString("email"),
                                                        resultat.getString("nomSociete"),
                                                        forme
                                                        );
                    
                    if (resultat.getString("numeroTVA")!=null) {
                        client.setNumeroTVA(resultat.getString("numeroTVA"));
                    }
                            
                    liste.add(client);
                }
                return liste;
            } catch(SQLException e) { 
                throw new ClientSocieteInexistantException(e);
            } finally { 
                if (resultat != null) try { resultat.close(); } catch(Exception e) { throw new ClientSocieteInexistantException(e);}
                if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {throw new ClientSocieteInexistantException(e);}
                    _connection.deconnecter();
            }
               /*
        //Methodes
	@Override
	public ClientSocieteDto rechercher(long numeroClient) throws ClientInexistantException {
                ResultSet resultat = null;
                ZoneDto zone = null;
                AdresseDto adresse = null;
                try {
                        //query
                        _callableStatement = _connection.getConnection().prepareCall("{call sp_SocieteSelectNumeroClient(?)}");
                        //parameter
                        _callableStatement.setLong("numeroClient",numeroClient);
                        //execute
                        resultat = _callableStatement.executeQuery();    
                	if(!resultat.next()) throw new ClientInexistantException();
			zone = new ZoneDto(resultat.getString("ZONE"));
                        adresse = new AdresseDto(   resultat.getString("RUE"),
                                                    resultat.getString("NUMERO"),
                                                    resultat.getString("CODEPOSTAL"),
                                                    resultat.getString("VILLE"),
                                                    resultat.getString("PAYS"),
                                                    zone);
                        return new ClientSocieteDto(    resultat.getLong("NUMEROCLIENT"),
                                                        resultat.getString("NOM"),
                                                        resultat.getString("PRENOM"),
                                                        adresse,
                                                        resultat.getString("TELEPHONE"),
                                                        resultat.getString("EMAIL"),
                                                        resultat.getString("NUMEROTVA"),
                                                        resultat.getString("SOCIETE"),
                                                        resultat.getString("STATUT"));
		}catch(SQLException e){throw new ClientInexistantException(e);
                }finally{           
                        if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {}
                        _connection.deconnecter();}
	}

        @Override
	public ClientSocieteDto rechercher(String numeroTVA) throws ClientInexistantException {
		ResultSet resultat = null;
                ZoneDto zone = null;
                AdresseDto adresse = null;
                try {
                        //query
                        _callableStatement = _connection.getConnection().prepareCall("{call sp_SocieteSelectNumeroTVA(?)}");
                        //parameter
                        _callableStatement.setString("numeroTVA",numeroTVA);
                        //execute
                        resultat = _callableStatement.executeQuery();    
                	if(!resultat.next()) throw new ClientInexistantException();
			zone = new ZoneDto(resultat.getString("ZONE"));
                        adresse = new AdresseDto(   resultat.getString("RUE"),
                                                    resultat.getString("NUMERO"),
                                                    resultat.getString("CODEPOSTAL"),
                                                    resultat.getString("VILLE"),
                                                    resultat.getString("PAYS"),
                                                    zone);
                        return new ClientSocieteDto(    resultat.getLong("NUMEROCLIENT"),
                                                        resultat.getString("NOM"),
                                                        resultat.getString("PRENOM"),
                                                        adresse,
                                                        resultat.getString("TELEPHONE"),
                                                        resultat.getString("EMAIL"),
                                                        resultat.getString("NUMEROTVA"),
                                                        resultat.getString("SOCIETE"),
                                                        resultat.getString("STATUT"));
		}catch(SQLException e){throw new ClientInexistantException(e);
                }finally{
                        if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {}
                        _connection.deconnecter();}
        }    
            
        @Override
	public ClientSocieteDto rechercher(String nom, String statut) throws ClientInexistantException {
                ResultSet resultat = null;
                ZoneDto zone = null;
                AdresseDto adresse = null;
                try {
                        //query
                        _callableStatement = _connection.getConnection().prepareCall("{call sp_SocieteSelectNomStatut(?,?)}");
                        //parameter
                        _callableStatement.setString("nom",nom);
                        _callableStatement.setString("statut",statut);
                        //execute
                        resultat = _callableStatement.executeQuery();    
                	if(!resultat.next()) throw new ClientInexistantException();
			zone = new ZoneDto(resultat.getString("ZONE"));
                        adresse = new AdresseDto(   resultat.getString("RUE"),
                                                    resultat.getString("NUMERO"),
                                                    resultat.getString("CODEPOSTAL"),
                                                    resultat.getString("VILLE"),
                                                    resultat.getString("PAYS"),
                                                    zone);
                        return new ClientSocieteDto(    resultat.getLong("NUMEROCLIENT"),
                                                        resultat.getString("NOM"),
                                                        resultat.getString("PRENOM"),
                                                        adresse,
                                                        resultat.getString("TELEPHONE"),
                                                        resultat.getString("EMAIL"),
                                                        resultat.getString("NUMEROTVA"),
                                                        resultat.getString("SOCIETE"),
                                                        resultat.getString("STATUT"));
		}catch(SQLException e){throw new ClientInexistantException(e);
                }finally{
                        if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {}
                        _connection.deconnecter();}
        }	
	        
	@Override
	public Vector<ClientSocieteDto> rechercher(AdresseDto adresse) throws ClientInexistantException {
		ResultSet resultat = null;
                ZoneDto zone = null;
                ClientSocieteDto client = null;
                try {
                        //query
                        _callableStatement = _connection.getConnection().prepareCall("{call sp_SocieteSelectAdresse(?,?)}");
                        //parameter
                        _callableStatement.setString("rue",adresse.getRue());
                        _callableStatement.setString("numero",adresse.getNumero());
                        _callableStatement.setString("codePostal",adresse.getCodePostal());
                        //execute
                        resultat = _callableStatement.executeQuery();    
                	if(!resultat.next()) throw new ClientInexistantException();
			Vector<ClientSocieteDto> liste = new Vector<ClientSocieteDto>();
			while(resultat.next()){
                                zone = new ZoneDto(resultat.getString("ZONE"));
                                adresse = new AdresseDto(   resultat.getString("RUE"),
                                                            resultat.getString("NUMERO"),
                                                            resultat.getString("CODEPOSTAL"),
                                                            resultat.getString("VILLE"),
                                                            resultat.getString("PAYS"),
                                                            zone);
                                client = new ClientSocieteDto(  resultat.getLong("NUMEROCLIENT"),
                                                                resultat.getString("NOM"),
                                                                resultat.getString("PRENOM"),
                                                                adresse,
                                                                resultat.getString("TELEPHONE"),
                                                                resultat.getString("EMAIL"),
                                                                resultat.getString("NUMEROTVA"),
                                                                resultat.getString("SOCIETE"),
                                                                resultat.getString("STATUT"));
                                liste.add(client);}
                        return liste;
                }catch(SQLException e){throw new ClientInexistantException(e);
                }finally{_connection.deconnecter();}
        }	
               */
    }
}